package com.firmboy.component.poi.controller;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.sql.DataSource;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.firmboy.demo.component.demo.bean.JdProduct;
import com.firmboy.demo.component.demo.service.JdProductService;

@RestController
@RequestMapping("/poi")
public class PoiController {
	
	private static final Logger log = LoggerFactory.getLogger(PoiController.class);
	
	@Autowired
	private DataSource dataSource;
	
	@Autowired
	private JdProductService jdProductService;
	
	@RequestMapping("/exportByMbatis")
	public void poiExportByMbatis(HttpServletRequest request){
		//1.创建一个xmls的excel文件，
		//2.查询数据库的数据，然后将数据写入到文件中
		//3.将文件下载到客户端
		long startTime = System.currentTimeMillis();
		String path = request.getSession().getServletContext().getRealPath("");
		try {
			Workbook xssfWorkbook = new XSSFWorkbook();//2007版
			Workbook hssfWorkbook = new HSSFWorkbook();//97-07版
			
			Sheet sheet = xssfWorkbook.createSheet();
			
			boolean flag = true;
			long pro = 100000;
			long start = 0;
			Map<String, Long> param = new HashMap<String,Long>();
			do{
				param.put("start", start);
				param.put("end", pro);
				List<JdProduct> jdProducts = jdProductService.getJdProducts(param);
				if(jdProducts.size()<10000){
					flag = false;
					break;
				}
				long page = start/pro+1;
				log.info("第"+page+"页，共"+jdProducts.size()+"条数据");
				start = start + pro;
			}while(flag);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();	
		}
		long endTime = System.currentTimeMillis();
		log.info("消耗时间："+(endTime-startTime));
	}
	
	@RequestMapping("/exportByJdbc")
	public void poiExportByJdbc(HttpServletRequest request){
		
		//1.创建一个xmls的excel文件，
		//2.查询数据库的数据，然后将数据写入到文件中
		//3.将文件下载到客户端
		long startTime = System.currentTimeMillis();
		String path = request.getSession().getServletContext().getRealPath("");
		try {
			Connection conn = dataSource.getConnection();
			PreparedStatement prepareStatement = conn.prepareStatement("select * from JD_PRODUCT limit ?,?");
			
			
			Workbook xssfWorkbook = new XSSFWorkbook();//2007版
			Workbook hssfWorkbook = new HSSFWorkbook();//97-07版
			
			Sheet sheet = xssfWorkbook.createSheet();
			
			boolean flag = true;
			long pro = 100000;
			long start = 0;
			Map<String, Long> param = new HashMap<String,Long>();
			do{
				param.put("start", start);
				param.put("end", pro);
				//List<JdProduct> jdProducts = jdProductService.getJdProducts(param);
				prepareStatement.setLong(1, start);
				prepareStatement.setLong(2, pro);
				ResultSet executeQuery = prepareStatement.executeQuery();
				int row = executeQuery.getRow();
				/*if(row<10000){
					flag = false;
					break;
				}*/
				long page = start/pro+1;
				if(page == 10){
					flag = false;
					break;
				}
				log.info("第"+page+"页，共条数据");
				start = start + pro;
			}while(flag);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			
		}
		long endTime = System.currentTimeMillis();
		log.info("消耗时间："+(endTime-startTime));
		
		
	}
}
